from sqlalchemy import create_engine
import pandas as pd
import openpyxl
import numpy as np
import os
import matplotlib.pyplot as plt
import datetime, random

plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

engine = create_engine(
    "mssql+pyodbc://pereader:pereader@172.18.65.31:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True)

engine2 = create_engine(
    "mssql+pyodbc://qatest:qatest@192.168.102.236:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True)


def format_excel(df, path, sheet_name):
    """
    调整excel格式
    :param df: 储存的数据
    :param path: 储存的位置
    :param sheet_name: 格式应用的表名
    :return: 
    """
    len1 = len(df)
    # 设置列-列宽字典
    col_width_one = {'A:A': 8.11, 'B:B': 10.89, 'C:C': 27.33, 'D:D': 12.44, 'E:E': 14.22, 'F:F': 8.11, }
    col_width_two = ['G:G', 'H:H', 'I:I', 'J:J', 'K:K', 'L:L']
    col_width_three = ['M:M', 'N:N', 'O:O', 'P:P', 'Q:Q', 'R:R', 'S:S', 'T:T', 'U:U', 'V:V', 'W:W', 'X:X', 'Y:Y', 'Z:Z',
                       'AA:AA', 'AB:AB', 'AC:AC', 'AD:AD', 'AE:AE', 'AF:AF', 'AG:AG', 'AH:AH', 'AI:AI', 'AJ:AJ',
                       'AK:AK',
                       'AL:AL', 'AM:AM', 'AN:AN', 'AO:AO', 'AP:AP', 'AQ:AQ', 'AR:AR', 'AS:AS', 'AT:AT', 'AU:AU',
                       'AV:AV']

    # df = pd.read_excel('F:/000-Work/WEEK35_3_副本.xlsx', engine='openpyxl', header=0)
    columns_name = pd.DataFrame(columns=df.columns)
    for i in df.columns:
        columns_name.loc[0, i] = i
    df = pd.concat([columns_name, df], axis=0)
    df.reset_index(drop=True, inplace=True)
    # print(df)

    writer = pd.ExcelWriter(path)
    df.to_excel(writer, '统计表', index=None, header=None)

    workbook = writer.book
    worksheets = writer.sheets
    change_sheet = worksheets[sheet_name]

    title_fmt_one = workbook.add_format(
        {"font_name": u"Courier New", "bold": True, "font_size": 8, "align": "center", "valign": "vcenter", 'border': 1,
         'text_wrap': True, })
    content_fmt_one = workbook.add_format(
        {"font_name": u"宋体", "bold": False, "font_size": 11, "align": "center", "valign": "bottom", 'border': 1,
         "bg_color": '#fde9d9'})
    content_fmt_two = workbook.add_format(
        {"font_name": u"宋体", "bold": False, "font_size": 11, "align": "center", "valign": "bottom", 'border': 1,
         "fg_color": '#daeef3'})
    content_fmt_three = workbook.add_format(
        {"font_name": u"宋体", "bold": False, "font_size": 11, "align": "center", "valign": "bottom", 'border': 1,
         "fg_color": '#daeef3', 'num_format': '0.0%'})
    content_fmt_four = workbook.add_format(
        {"font_name": u"宋体", "bold": False, "font_size": 11, "align": "center", "valign": "bottom", 'border': 1,
         "fg_color": '#e4dfec'})
    content_fmt_bad = workbook.add_format(
        {"font_name": u"宋体", "bold": False, "font_size": 11, "align": "center", "valign": "bottom", 'border': 1,
         'font_color': 'red', "bg_color": '#ffc7ce'})

    for k, v in col_width_one.items():
        change_sheet.set_column(k, v, cell_format=content_fmt_one)
    for k in col_width_two:
        if k == 'G:G':
            change_sheet.set_column(k, 8.11, cell_format=content_fmt_two)
        else:
            change_sheet.set_column(k, 8.11, cell_format=content_fmt_three)
    for k in col_width_three:
        if k not in ['R:R', 'S:S', 'T:T', 'U:U']:
            change_sheet.set_column(k, 8.11, cell_format=content_fmt_four)
        else:
            change_sheet.set_column(k, 9.78, cell_format=content_fmt_four)

    change_sheet.conditional_format(f'L2:L{len1}',
                                    {'type': 'cell', 'criteria': '<=', 'value': 0.95, 'format': content_fmt_bad})
    change_sheet.merge_range('C1:D1', 'MODELCODE')
    change_sheet.set_row(0, 33, cell_format=title_fmt_one)

    # writer.save()
    return writer


def Data_save(df_info, ITEM_NAME_list, dict1):
    """

    :param dict1: 文件存储路径字典
    :param df_info:
    :param ITEM_NAME_list:
    :return:
    """
    # start_point = 0
    start_num = 1
    index = 0
    index2 = 0
    df_list = []
    drop_list = []
    # 创建统计表表头
    df_Rate = pd.DataFrame(
        columns=['WEEK', 'TH DATE', 'MODELCODE', 'LOST', 'LOTNO', 'TH_EQP_ID', 'IN_QTY', 'MAC1_RATIO',
                 'MAC2 ONLY_RATIO',
                 'MAC2 TOTAL_RATIO', 'MAC3 ONLY_RATIO', 'MAC3 TOTAL_RATIO', 'VF_MIN', 'VF_AVG', 'VF_MAX',
                 'VF_MEDIAN', 'VF_STD', 'IV_MIN', 'IV_AVG', 'IV_MAX', 'IV_MEDIAN', 'IV_STD', 'X', 'X_STD', 'Y',
                 'Y_STD',
                 'RA_MIN', 'RA_AVG', 'RA_MAX', 'RA_MEDIAN', 'RA_STD', 'R9_MIN', 'R9_AVG', 'R9_MAX', 'R9_MEDIAN',
                 'R9_STD', 'CF2_dU', 'CF2_dx', 'CF2_dy', 'CF2_dRa', 'CF2_dR9', 'CF2_lm', 'CF3_dU', 'CF3_dx',
                 'CF3_dy',
                 'CF3_dRa', 'CF3_dR9', 'CF3_lm'])
    # 创建空DataFrame
    df_Data = pd.DataFrame()
    df_Data2 = pd.DataFrame()
    df_delete = pd.DataFrame()

    #
    writer2 = pd.ExcelWriter(dict1['工单信息'])
    for ITEM_NAME in ITEM_NAME_list:
        # 获取光通量上下限
        MIN = int(df_info.loc[df_info['型号'] == ITEM_NAME]['光通量下限'].to_list()[0])
        MAX = int(df_info.loc[df_info['型号'] == ITEM_NAME]['光通量上限'].to_list()[0])
        # 获取工单列表
        mo_list = list(df_info.loc[df_info['型号'] == ITEM_NAME]['MoID'])
        print(f"{ITEM_NAME}\n当前工单:{mo_list}")

        sql_a = f"SELECT TOP {5 + len(mo_list)} DOC_NO FROM MO_ITEM WHERE ITEM_NAME='{ITEM_NAME}' AND DOC_NO like" \
                f" '5101%' ORDER BY CreateDate DESC"
        df_temp_a = pd.read_sql(sql_a, engine)

        all_mo_list = list(df_temp_a['DOC_NO'])
        # lm_dict = {}
        if all_mo_list:
            print('画图开始！')
            df_lm = Anlysis(tuple(all_mo_list), ITEM_NAME, MIN, MAX, mo_list, dict1)
            # print(df_lm)
            print('画图结束！')
            df_lm.rename(columns={'LuminousFlux_lm_mean': 'after_avg', '系数': 'lm_gain'}, inplace=True)

            print('光通量系数存储')
            remove_list = []
            df_lm['before_avg'] = 0
            sql2 = f"SELECT MoID, after_avg FROM MO_lm"
            df_2 = pd.read_sql(sql2, engine2)
            list2 = list(df_2['MoID'])
            for i in df_lm.index.values:
                if df_lm.loc[i, 'MoID'] in list2:
                    remove_list.append(i)
                df_lm.loc[i, 'before_avg'] = round(df_lm.loc[i, 'after_avg'] / df_lm.loc[i, 'lm_gain'], 2)

            df_save = df_lm[['MoID', 'before_avg', 'after_avg', 'lm_gain']]
            df_save['lm_MIN'] = MIN
            df_save['lm_MAX'] = MAX
            df_save['ITEM_NAME'] = ITEM_NAME
            df_save['lm_AVG'] = round((MAX + MIN) / 2, 2)

            df_save.drop(index=remove_list, inplace=True)
            df_save.drop_duplicates(inplace=True)
            df_save = df_save.reset_index(drop=True)
            df_save.to_sql('MO_lm', engine2, if_exists='append', index=False)
            print('光通量系数存储完成！')

        for mo_id in mo_list:
            print(mo_id)
            MAC1 = 0
            MAC2 = 0
            MAC3 = 0

            if not df_lm.empty and mo_id in list(df_lm['MoID']):
                num = df_lm[df_lm['MoID'] == mo_id]['lm_gain'].to_list()[0]
            else:
                num = 1
            print(num)

            sql1 = f"SELECT DISTINCT LOTID, MachineID FROM Sorting_Info WHERE MoID = '{mo_id}' AND ((Notes not like '%抽测%' " \
                   "AND Notes not like '%远方1号%') AND (OriginalFileName not like '%抽测%' " \
                   "AND OriginalFileName not like '%远方1号%'))"
            df_get = pd.read_sql(sql1, engine)

            get_list = list(set(df_get['LOTID']))
            get_tuple = tuple(get_list)
            print(f"批次号:{get_tuple}")
            if not get_list:
                continue
            elif len(get_list) == 1:
                sql2 = f"SELECT * FROM Sorting_Data WHERE MoID='{mo_id}' AND LotID={get_tuple[0]}"
            else:
                sql2 = f"SELECT * FROM Sorting_Data WHERE MoID='{mo_id}' AND LotID in {get_tuple}"
            # print(sql2)
            df_temp = pd.read_sql(sql2, engine)
            total = len(df_temp)
            print(f"不符数据删除前:{total}")
            EQP_set = set()
            for i, j in zip(list(df_get['LOTID']), list(df_get['MachineID'])):
                if j in [2, 3, 4, 5, 22]:
                    EQP_set.add(j)
            EQP_list = ','.join(str(i) for i in list(EQP_set))
            print(f"机台号:{EQP_set}")
            # ��Ӧ��̨����ֵ��ʹ��Sorting_BIN��ɸѡһ������������
            if len(list(EQP_set)) >= 1:
                # ���������׼DataFrame
                if len(list(EQP_set)) == 1:
                    sql_QStandard = f"SELECT * FROM Sorting_BIN WHERE QStandard=(SELECT FEATURE_VALUE FROM " \
                                    f"ITEM_CODE_VALUE WHERE FEATURE_NAME='质量标准' AND ITEM_CODE=(SELECT ITEM_CODE " \
                                    f"FROM MO_ITEM WHERE DOC_NO='{mo_id}')) COLLATE Chinese_PRC_CS_AS AND MachineID" \
                                    f"={list(EQP_set)[0]}"
                else:
                    sql_QStandard = f"SELECT * FROM Sorting_BIN WHERE QStandard=(SELECT FEATURE_VALUE FROM " \
                                    f"ITEM_CODE_VALUE WHERE FEATURE_NAME='质量标准' AND ITEM_CODE=(SELECT ITEM_CODE " \
                                    f"FROM MO_ITEM WHERE DOC_NO='{mo_id}')) COLLATE Chinese_PRC_CS_AS AND MachineID" \
                                    f" in {tuple(EQP_set)}"
                df_QStandard = pd.read_sql(sql_QStandard, engine)

                df_QStandard = df_QStandard[['QStandard', 'MachineID', 'BinID', 'BinName']]
                df_bin = pd.merge(df_get, df_QStandard, on='MachineID')
                df_bin.drop(columns=['MachineID', 'QStandard'], inplace=True)
                print(df_bin)
                bin_list = []
                ng_list = []
                for i in range(len(df_bin)):
                    if df_bin.loc[i]['BinName'].find('MAC1') >= 0:
                        bin_list.append(i)
                    elif df_bin.loc[i]['BinName'].find('MAC2') >= 0:
                        bin_list.append(i)
                    elif df_bin.loc[i]['BinName'].find('MAC3') >= 0:
                        bin_list.append(i)
                    else:
                        ng_list.append(i)
                df_OKbin = df_bin.loc[bin_list]
                df_NGbin = df_bin.loc[ng_list]
                df_OKbin.rename(columns={'LOTID': 'LotID', 'BinName': 'OKName'}, inplace=True)
                df_NGbin.rename(columns={'LOTID': 'LotID', 'BinName': 'NGName'}, inplace=True)

                df_ng = pd.merge(df_temp, df_NGbin, on=['LotID', 'BinID'])
                df_ng.reset_index(drop=True, inplace=True)
                print(f"NG:{len(df_ng)}")
                df_temp = pd.merge(df_temp, df_OKbin, on=['LotID', 'BinID'])
                print(f"bin:{len(df_temp)}")
                df_temp.reset_index(drop=True, inplace=True)
                for a in range(len(df_temp)):
                    if df_temp.loc[a]['OKName'].find('MAC1') >= 0:
                        MAC1 += 1
                    elif df_temp.loc[a]['OKName'].find('MAC2') >= 0:
                        MAC2 += 1
                    elif df_temp.loc[a]['OKName'].find('MAC3') >= 0:
                        MAC3 += 1
            else:
                df_ng = df_temp[df_temp['BinID'] > 3]
                # print(df_ng)
                df_temp = df_temp[df_temp['BinID'] <= 3]
                df_temp.reset_index(drop=None, inplace=True)
                for a in range(len(df_temp)):
                    if df_temp.loc[a]['BinID'] == 1:
                        MAC1 += 1
                    elif df_temp.loc[a]['BinID'] == 2:
                        MAC2 += 1
                    elif df_temp.loc[a]['BinID'] == 3:
                        MAC3 += 1

            df_unchange = df_temp
            df_temp['LuminousFlux_lm'] = df_temp['LuminousFlux_lm'] * num
            df_temp['LumiousEfficacy_lmPerW'] = df_temp['LumiousEfficacy_lmPerW'] * num
            print(f"不符数据删除前:{len(df_temp)}")
            df_temp = df_temp[df_temp['LuminousFlux_lm'] <= MAX]
            df_temp = df_temp[df_temp['LuminousFlux_lm'] >= MIN]
            print(f"不符数据删除后:{len(df_temp)}")
            if len(list(EQP_set)) >= 1:
                # df_temp = df_temp.loc[df_temp['OKName'] != None]
                df_temp.drop(columns=['OKName'], inplace=True)
            if df_temp.empty:
                print(f"无合格数据:{mo_id}")
                df_over = df_unchange
            else:
                df_over = df_temp
            # print(df_ng)
            df_temp = pd.concat([df_over, df_ng], axis=0)
            # print(len(df_temp))

            CF3 = pd.read_sql(f"SELECT ITEM_DESC FROM MO_ITEM WHERE DOC_NO='{mo_id}'", engine)
            CF3['ITEM_DESC'] = CF3['ITEM_DESC'].apply(lambda x: x.split("CF3代码:")[-1].split(';')[0])
            CF3 = CF3.loc[0, 'ITEM_DESC']
            df_cf3 = pd.read_sql(f"SELECT top 1 * FROM CF3_Customer WHERE CF3_Code='{CF3}' ORDER BY EffectiveDate desc",
                                 engine)
            CF2 = pd.read_sql(f"SELECT ITEM_DESC FROM MO_ITEM WHERE DOC_NO='{mo_id}'", engine)
            CF2['ITEM_DESC'] = CF2['ITEM_DESC'].apply(lambda x: x.split("CF2代码:")[-1].split(';')[0])
            CF2 = CF2.loc[0, 'ITEM_DESC']
            df_cf2 = pd.read_sql(f"SELECT top 1 * FROM CF2_LEDteen WHERE CF2_Code='{CF2}' ORDER BY EffectiveDate desc",
                                 engine)
            sql_b = f"SELECT CONVERT(INTEGER,PLAN_QTY) AS 数量 FROM MO_ITEM WHERE DOC_NO='{mo_id}'"
            df_qty = pd.read_sql(sql_b, engine)
            Plan_QTY = df_qty.loc[0, '数量']

            if round((MAC1 + MAC2 + MAC3) / total, 2) > 1 or round((MAC1 + MAC2 + MAC3) / total, 2) < 0.79:
                drop_list.append(mo_id)
                if df_Data2.empty:
                    df_Data2 = df_temp
                else:
                    df_Data2 = pd.concat([df_temp, df_Data2], axis=0)

                df_delete.loc[index, 'WEEK'] = int(datetime.datetime.strptime(end_time, '%Y.%m.%d').strftime('%W')) + 1
                print(df_temp['U01'].min())
                df_delete.loc[index, 'TH DATE'] = datetime.datetime.strftime(df_temp['U01'].min().to_pydatetime(),
                                                                             "%Y-%m-%d") if df_temp[
                                                                                                'TestTime'].min() is not np.nan else ''
                df_delete.loc[index, 'MODELCODE'] = ITEM_NAME
                df_delete.loc[index, 'LOTNO'] = mo_id
                df_delete.loc[index, 'TH_EQP_ID'] = EQP_list
                df_delete.loc[index, 'IN_QTY'] = Plan_QTY
                df_delete.loc[index, 'MAC1_RATIO'] = round(MAC1 / total, 2)
                df_delete.loc[index, 'MAC2 ONLY_RATIO'] = round(MAC2 / total, 2)
                df_delete.loc[index, 'MAC2 TOTAL_RATIO'] = round((MAC1 + MAC2) / total, 2)
                df_delete.loc[index, 'MAC3 ONLY_RATIO'] = round(MAC3 / total, 2)
                df_delete.loc[index, 'MAC3 TOTAL_RATIO'] = round((MAC1 + MAC2 + MAC3) / total, 2)

                df_delete.loc[index, 'VF_MIN'] = round(df_temp['ForwardVoltage_V'].min(), 2)
                df_delete.loc[index, 'VF_AVG'] = round(df_temp['ForwardVoltage_V'].mean(), 2)
                df_delete.loc[index, 'VF_MAX'] = round(df_temp['ForwardVoltage_V'].max(), 2)
                df_delete.loc[index, 'VF_MEDIAN'] = round(df_temp['ForwardVoltage_V'].median(), 2)
                df_delete.loc[index, 'VF_STD'] = round(df_temp['ForwardVoltage_V'].std(), 2)

                df_delete.loc[index, 'IV_MIN'] = round(df_temp['LuminousFlux_lm'].min(), 2)
                df_delete.loc[index, 'IV_AVG'] = round(df_temp['LuminousFlux_lm'].mean(), 2)
                df_delete.loc[index, 'IV_MAX'] = round(df_temp['LuminousFlux_lm'].max(), 2)
                df_delete.loc[index, 'IV_MEDIAN'] = round(df_temp['LuminousFlux_lm'].median(), 2)
                df_delete.loc[index, 'IV_STD'] = round(df_temp['LuminousFlux_lm'].std(), 2)

                df_delete.loc[index, 'X'] = round(df_temp['CIEx'].mean(), 4)
                df_delete.loc[index, 'X_STD'] = round(df_temp['CIEx'].std(), 4)
                df_delete.loc[index, 'Y'] = round(df_temp['CIEy'].mean(), 4)
                df_delete.loc[index, 'Y_STD'] = round(df_temp['CIEy'].std(), 4)

                df_delete.loc[index, 'RA_MIN'] = round(df_temp['Ra'].min(), 2)
                df_delete.loc[index, 'RA_AVG'] = round(df_temp['Ra'].mean(), 2)
                df_delete.loc[index, 'RA_MAX'] = round(df_temp['Ra'].max(), 2)
                df_delete.loc[index, 'RA_MEDIAN'] = round(df_temp['Ra'].median(), 2)
                df_delete.loc[index, 'RA_STD'] = round(df_temp['Ra'].std(), 2)

                df_delete.loc[index, 'R9_MIN'] = round(df_temp['R9'].min(), 2)
                df_delete.loc[index, 'R9_AVG'] = round(df_temp['R9'].mean(), 2)
                df_delete.loc[index, 'R9_MAX'] = round(df_temp['R9'].max(), 2)
                df_delete.loc[index, 'R9_MEDIAN'] = round(df_temp['R9'].median(), 2)
                df_delete.loc[index, 'R9_STD'] = round(df_temp['R9'].std(), 2)

                df_delete.loc[index, 'CF2_dU'] = round(df_cf2.loc[0, 'ForwardVoltage_offset'], 4)
                df_delete.loc[index, 'CF2_dx'] = round(df_cf2.loc[0, 'CIEx_offset'], 4)
                df_delete.loc[index, 'CF2_dy'] = round(df_cf2.loc[0, 'CIEy_offset'], 4)
                df_delete.loc[index, 'CF2_dRa'] = round(df_cf2.loc[0, 'CRI_offset'], 4)
                df_delete.loc[index, 'CF2_dR9'] = round(df_cf2.loc[0, 'R9_offset'], 4)
                df_delete.loc[index, 'CF2_lm'] = round(df_cf2.loc[0, 'LuminousFlux_gain'], 4)

                df_delete.loc[index, 'CF3_dU'] = round(df_cf3.loc[0, 'ForwardVoltage_offset'], 4)
                df_delete.loc[index, 'CF3_dx'] = round(df_cf3.loc[0, 'CIEx_offset'], 4)
                df_delete.loc[index, 'CF3_dy'] = round(df_cf3.loc[0, 'CIEy_offset'], 4)
                df_delete.loc[index, 'CF3_dRa'] = round(df_cf3.loc[0, 'CRI_offset'], 4)
                df_delete.loc[index, 'CF3_dR9'] = round(df_cf3.loc[0, 'R9_offset'], 4)
                df_delete.loc[index, 'CF3_lm'] = round(df_cf3.loc[0, 'LuminousFlux_gain'], 4)
                index2 += 1
            else:
                if df_Data.empty:
                    df_Data = df_temp
                else:
                    if len(df_Data) >= 120000:
                        df_list.append(df_Data)
                        df_Data = df_temp
                    else:
                        df_Data = pd.concat([df_temp, df_Data], axis=0)

                df_Rate.loc[index, 'WEEK'] = int(datetime.datetime.strptime(end_time, '%Y.%m.%d').strftime('%W')) + 1
                print(df_temp['TestTime'].min())
                df_Rate.loc[index, 'TH DATE'] = datetime.datetime.strftime(df_temp['TestTime'].min(), "%Y-%m-%d") \
                    if df_temp['TestTime'].min() is not np.nan else ''
                df_Rate.loc[index, 'MODELCODE'] = ITEM_NAME
                df_Rate.loc[index, 'LOTNO'] = mo_id
                df_Rate.loc[index, 'TH_EQP_ID'] = EQP_list

                df_Rate.loc[index, 'IN_QTY'] = Plan_QTY
                df_Rate.loc[index, 'MAC1_RATIO'] = round(MAC1 / total, 2)
                df_Rate.loc[index, 'MAC2 ONLY_RATIO'] = round(MAC2 / total, 2)
                df_Rate.loc[index, 'MAC2 TOTAL_RATIO'] = round((MAC1 + MAC2) / total, 2)
                df_Rate.loc[index, 'MAC3 ONLY_RATIO'] = round(MAC3 / total, 2)
                df_Rate.loc[index, 'MAC3 TOTAL_RATIO'] = round((MAC1 + MAC2 + MAC3) / total, 2)


                df_Rate.loc[index, 'VF_MIN'] = round(df_temp['ForwardVoltage_V'].min(), 2)
                df_Rate.loc[index, 'VF_AVG'] = round(df_temp['ForwardVoltage_V'].mean(), 2)
                df_Rate.loc[index, 'VF_MAX'] = round(df_temp['ForwardVoltage_V'].max(), 2)
                df_Rate.loc[index, 'VF_MEDIAN'] = round(df_temp['ForwardVoltage_V'].median(), 2)
                df_Rate.loc[index, 'VF_STD'] = round(df_temp['ForwardVoltage_V'].std(), 2)

                df_Rate.loc[index, 'IV_MIN'] = round(df_temp['LuminousFlux_lm'].min(), 2)
                df_Rate.loc[index, 'IV_AVG'] = round(df_temp['LuminousFlux_lm'].mean(), 2)
                df_Rate.loc[index, 'IV_MAX'] = round(df_temp['LuminousFlux_lm'].max(), 2)
                df_Rate.loc[index, 'IV_MEDIAN'] = round(df_temp['LuminousFlux_lm'].median(), 2)
                df_Rate.loc[index, 'IV_STD'] = round(df_temp['LuminousFlux_lm'].std(), 2)

                df_Rate.loc[index, 'X'] = round(df_temp['CIEx'].mean(), 4)
                df_Rate.loc[index, 'X_STD'] = round(df_temp['CIEx'].std(), 4)
                df_Rate.loc[index, 'Y'] = round(df_temp['CIEy'].mean(), 4)
                df_Rate.loc[index, 'Y_STD'] = round(df_temp['CIEy'].std(), 4)

                df_Rate.loc[index, 'RA_MIN'] = round(df_temp['Ra'].min(), 2)
                df_Rate.loc[index, 'RA_AVG'] = round(df_temp['Ra'].mean(), 2)
                df_Rate.loc[index, 'RA_MAX'] = round(df_temp['Ra'].max(), 2)
                df_Rate.loc[index, 'RA_MEDIAN'] = round(df_temp['Ra'].median(), 2)
                df_Rate.loc[index, 'RA_STD'] = round(df_temp['Ra'].std(), 2)

                df_Rate.loc[index, 'R9_MIN'] = round(df_temp['R9'].min(), 2)
                df_Rate.loc[index, 'R9_AVG'] = round(df_temp['R9'].mean(), 2)
                df_Rate.loc[index, 'R9_MAX'] = round(df_temp['R9'].max(), 2)
                df_Rate.loc[index, 'R9_MEDIAN'] = round(df_temp['R9'].median(), 2)
                df_Rate.loc[index, 'R9_STD'] = round(df_temp['R9'].std(), 2)

                df_Rate.loc[index, 'CF2_dU'] = round(df_cf2.loc[0, 'ForwardVoltage_offset'], 4)
                df_Rate.loc[index, 'CF2_dx'] = round(df_cf2.loc[0, 'CIEx_offset'], 4)
                df_Rate.loc[index, 'CF2_dy'] = round(df_cf2.loc[0, 'CIEy_offset'], 4)
                df_Rate.loc[index, 'CF2_dRa'] = round(df_cf2.loc[0, 'CRI_offset'], 4)
                df_Rate.loc[index, 'CF2_dR9'] = round(df_cf2.loc[0, 'R9_offset'], 4)
                df_Rate.loc[index, 'CF2_lm'] = round(df_cf2.loc[0, 'LuminousFlux_gain'], 4)

                df_Rate.loc[index, 'CF3_dU'] = round(df_cf3.loc[0, 'ForwardVoltage_offset'], 4)
                df_Rate.loc[index, 'CF3_dx'] = round(df_cf3.loc[0, 'CIEx_offset'], 4)
                df_Rate.loc[index, 'CF3_dy'] = round(df_cf3.loc[0, 'CIEy_offset'], 4)
                df_Rate.loc[index, 'CF3_dRa'] = round(df_cf3.loc[0, 'CRI_offset'], 4)
                df_Rate.loc[index, 'CF3_dR9'] = round(df_cf3.loc[0, 'R9_offset'], 4)
                df_Rate.loc[index, 'CF3_lm'] = round(df_cf3.loc[0, 'LuminousFlux_gain'], 4)
                index += 1
    df_list.append(df_Data)
    for df in df_list:
        path = os.path.join(dict1['分光数据'],
               f"WEEK{int(datetime.datetime.strptime(end_time, '%Y.%m.%d').strftime('%W')) + 1}_{start_num}.xlsx")
        # ���ø�ʽ

        writer = format_excel(df_Rate, path, '统计表')
        # df_Rate.to_excel(writer, '统计表', index=None)
        # df = df.sort_values(by=['ID'])
        df.to_excel(writer, 'TH DATA', index=None)
        writer.save()
        start_num += 1
    df_delete.to_excel(writer2, '统计表', index=None)
    df_Data2.to_excel(writer2, 'TH DATA', index=None)
    writer2.save()

    print('分光数据获取完成！')
    return drop_list


def Anlysis(tuple1, ITEM_NAME, MIN, MAX, list2, dict1):
    """
    :param dict1: 文件存储路径字典
    :param tuple1:
    :param ITEM_NAME:
    :param MIN:
    :param MAX:
    :param list2:
    :return:
    """
    if len(tuple1) == 1:
        sql1 = f"SELECT MoID,LuminousFlux_lm_mean FROM Sorting_Data_Analysis WHERE MoID='{tuple1[0]}' AND " \
               f"Total_Counts=(SELECT MAX(Total_Counts) FROM Sorting_Data_Analysis WHERE MoID='{tuple1[0]}')"
    else:
        sql1 = f"SELECT A.* FROM (SELECT MoID,LuminousFlux_lm_mean,Total_Counts FROM Sorting_Data_Analysis WHERE MoID IN {tuple1}) A " \
               f"JOIN (SELECT MoID,MAX(Total_Counts) AS Total_Counts FROM Sorting_Data_Analysis WHERE MoID IN {tuple1} GROUP BY MoID) B " \
               "ON A.MoID=B.MoID AND A.Total_Counts=B.Total_Counts"
    df = pd.read_sql(sql1, engine)

    mo_list = list(tuple1)
    if len(mo_list) > len(list(df['MoID'])):
        f.write(f"{ITEM_NAME}-型号异常:\n")
    for id in mo_list:
        if id not in list(df['MoID']):
            f.write(f"{id}--该工单无数据\n")
    sql2 = f"SELECT MoID,after_avg,lm_gain FROM MO_lm"
    df_2 = pd.read_sql(sql2, engine2)

    df2 = pd.DataFrame(columns=['MoID', 'LuminousFlux_lm_mean', '系数', '差异'])
    df2['MoID'] = df['MoID']
    df2['LuminousFlux_lm_mean'] = df['LuminousFlux_lm_mean']
    df2['LuminousFlux_lm_mean'] = df2['LuminousFlux_lm_mean'].apply(lambda x: round(float(x), 2))
    df2['系数'] = 1
    # df2.to_excel(f"{ITEM_NAME}_ԭʼ.xls", sheet_name=f'{ITEM_NAME}', index=False)
    avg = (MIN + MAX) / 2
    avg2 = df2['LuminousFlux_lm_mean'].mean() / avg - 1
    df2['差异'] = df2['LuminousFlux_lm_mean'] / avg - 1
    # print(df2['差异'])

    for i in range(len(df2)):
        if df2.loc[i, 'MoID'] in list(df_2['MoID']):
            df2.loc[i, 'LuminousFlux_lm_mean'] = df_2.loc[df_2['MoID'] == df2.loc[i, 'MoID']]['after_avg'].to_list()[0]
            df2.loc[i, '差异'] = df2.loc[i, 'LuminousFlux_lm_mean'] / avg - 1
            df2.loc[i, '系数'] = df_2.loc[df_2['MoID'] == df2.loc[i, 'MoID']]['lm_gain'].to_list()[0]
            continue
        elif df2.loc[i, 'MoID'] not in list2:
            continue
        if (df2.loc[i, '差异'] > 0.02 or df2.loc[i, '差异'] < -0.02) and len(df2) > 1:
            before = df2.loc[i, '差异']
            while df2.loc[i, '差异'] < -0.02 or df2.loc[i, '差异'] > 0.02:
                suiji_num = round(random.uniform(0.005, 0.01), 3)
                change = df2.loc[i, '差异']
                if df2.loc[i, '差异'] > 0.02:
                    df2.loc[i, '差异'] = change - suiji_num
                elif df2.loc[i, '差异'] < -0.02:
                    df2.loc[i, '差异'] = change + suiji_num
            print(df2.loc[i, '差异'])
            df2.loc[i, '系数'] = round((df2.loc[i, '差异'] + 1) / (before + 1), 4)
            df2.loc[i, 'LuminousFlux_lm_mean'] = round(df2.loc[i, 'LuminousFlux_lm_mean'] * df2.loc[i, '系数'], 2)
    x1 = df2['MoID']
    y2 = [df2.loc[df2['MoID'] == x]['差异'].to_list()[0] for x in x1]
    y3 = [0.03 for i in x1]
    y4 = [-0.03 for i in x1]
    y5 = [avg2 for i in x1]
    fig = plt.figure(figsize=(16, 10))

    plt.plot(x1, y2, label='lm平均值趋势', color='green')
    plt.plot(x1, y3, color='red', ls=':')
    plt.plot(x1, y4, color='red', ls=':')
    plt.plot(x1, y5, color='blue', ls='--')

    for a, b in zip(x1, y2):
        if b is not None:
            plt.text(a, b, round(b, 4), ha='center', va='bottom')
    # for a, b in zip(x1, y1):
    #     if b is not None:
    #         if b>0.02 or b<-0.02:
    #             plt.text(a, b, round(b, 4), ha='center', va='bottom')
    plt.xlabel('订单号')
    plt.xticks(rotation=90)
    plt.ylabel('lm平均值')
    plt.title(f'lm平均值趋势')
    plt.legend()
    plt.savefig(os.path.join(dict1['趋势图'],"{ITEM_NAME}.jpg"))
    # plt.show()
    plt.close()
    return df2


def get_data(start, end, dict1):
    """
    获取工单信息
    :param dict1: 文件存储路径字典
    :param start:
    :param end:
    :return:
    """
    sql1 = "SELECT DISTINCT MoID FROM Sorting_Data WHERE MoID like '5101%'"
    df_1 = pd.read_sql(sql1, engine)

    sql2 = f"SELECT DISTINCT MONO AS MoID FROM LOTError WHERE EVENTDATE BETWEEN CONVERT(datetime,'{start}',102) AND " \
           f"CONVERT(datetime,'{end}',102)"
    df_2 = pd.read_sql(sql2, engine)

    sql3 = "SELECT DOC_NO AS MoID,ITEM_NAME AS 型号,ITEM_SPECIFICATION FROM MO_ITEM WHERE DOC_NO " \
           "IN (SELECT DISTINCT DOC_NO FROM MO_DATE WHERE CUSTOMER_CODE='C0001')"
    df_3 = pd.read_sql(sql3, engine)

    list_min = []
    list_max = []

    def get_lm(x):
        """

        :param x:
        :return:
        """
        try:
            str1 = x.split('流明lm:')[1].split(';')[0]
        except:
            str1 = ''
        if str1 and len(str1.split('-')) > 1:
            list_min.append(str1.split('-')[0])
            list_max.append(str1.split('-')[1])
        else:
            list_min.append('')
            list_max.append('')

    df_3['ITEM_SPECIFICATION'].apply(lambda x: get_lm(x))

    df_3['光通量下限'] = list_min
    df_3['光通量上限'] = list_max
    df_3 = df_3[['MoID', '型号', '光通量下限', '光通量上限']]

    df_temp = pd.merge(df_1, df_2, on='MoID')
    df_info = pd.merge(df_temp, df_3, on='MoID')
    df_info.rename(columns={'MoID': '工单'})
    df_info.to_excel(dict1['工单信息'], index=False)
    # df_info.to_excel(f"./工单信息.xlsx", index=False)
    return df_info


def save_reject_info(list1, dict1):
    """

    :param dict1: ·���ֵ�
    :param list1: ����
    :return:
    """
    sql_s = f"select * from MO_lm where MoID in ('{','.join(list1)}') and (lm_gain>1.025 or lm_gain<0.975)"
    df2 = pd.read_sql(sql_s, engine2)
    if not df2.empty:
        df2.to_excel(os.path.join(dict1['分光数据'], '需改善数据.xlsx'), index=False)


def make_dir(end):
    dict1 = {}
    base_path = r"W:\内部文件夹\0 客户对应组\3 数据\分光数据"
    year_path = os.path.join(base_path, f"{int(datetime.datetime.strptime(end, '%Y.%m.%d').strftime('%Y'))}")
    if not os.path.exists(year_path):
        os.makedirs(year_path)

    info_path = os.path.join(year_path, "工单信息")
    if not os.path.exists(info_path):
        os.makedirs(info_path)

    picture_path = os.path.join(year_path, "趋势图")
    if not os.path.exists(picture_path):
        os.makedirs(picture_path)

    data_path = os.path.join(year_path, "数据")
    if not os.path.exists(data_path):
        os.makedirs(data_path)

    # week_path = os.path.join(year_path, f"WEEK{int(datetime.datetime.strptime(end, '%Y.%m.%d').strftime('%W')) + 1}")
    # if not os.path.exists(week_path):
    #     os.makedirs(week_path)

    path1 = os.path.join(info_path,
                         f"{int(datetime.datetime.strptime(end, '%Y.%m.%d').strftime('%W')) + 1}周无数据工单记录.txt")
    dict1['无数据工单'] = path1

    path2 = os.path.join(info_path,
                         f"{int(datetime.datetime.strptime(end, '%Y.%m.%d').strftime('%W')) + 1}工单信息.xlsx")
    dict1['工单信息'] = path2

    path3 = os.path.join(picture_path, f"{int(datetime.datetime.strptime(end, '%Y.%m.%d').strftime('%W')) + 1}周")
    if not os.path.exists(path3):
        os.makedirs(path3)
    dict1['趋势图'] = path3

    path4 = os.path.join(data_path, f"WEEK{int(datetime.datetime.strptime(end, '%Y.%m.%d').strftime('%W')) + 1}")
    if not os.path.exists(path4):
        os.makedirs(path4)
    dict1['分光数据'] = path4
    return dict1


if __name__ == '__main__':
    start_time = '2021.8.20'
    # end_time =
    now = datetime.datetime.now()
    delta1 = datetime.timedelta(days=1)
    delta2 = datetime.timedelta(days=7)
    n_days_1 = now - delta1
    n_days_2 = now - delta2
    start_time = n_days_2.strftime('%Y.%m.%d')
    end_time = n_days_1.strftime('%Y.%m.%d')

    dict1 = make_dir(end_time)
    f = open(dict1['无数据工单'], 'w')

    print('获取工单数据')
    df_info = get_data(start_time, end_time, dict1)

    MO_list = list(df_info['MoID'])
    # print(df_info)
    print('工单数据获取完成')

    ITEM_NAME_list = list(set(list(df_info['型号'])))
    drop_list = Data_save(df_info, ITEM_NAME_list, dict1)
    f.close()
    save_reject_info(drop_list, dict1)
    # # make_dir('2022.8.26')
